<%@taglib uri="http://www.springframework.org/tags/form" prefix="form"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%@ page session="true" %>
<c:set var="baseURL" value="${pageContext.request.localName}"/> 
<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Fresh Direct</title>

    <!-- Bootstrap -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.1/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.1/css/bootstrap-theme.min.css">
    <link href='http://fonts.googleapis.com/css?family=Oswald' rel='stylesheet' type='text/css'>
    <!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
    <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
    <!--[if lt IE 9]>
      <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
      <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
<!-- DataTables CSS -->
    <!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
    <!-- Include all compiled plugins (below), or include individual files as needed -->
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.1/js/bootstrap.min.js"></script>
<link rel="stylesheet" type="text/css" href="//netdna.bootstrapcdn.com/bootstrap/3.0.3/css/bootstrap.min.css">
		<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/plug-ins/9dcbecd42ad/integration/bootstrap/3/dataTables.bootstrap.css">

		<script type="text/javascript" language="javascript" src="//code.jquery.com/jquery-1.10.2.min.js"></script>
		<script type="text/javascript" language="javascript" src="//cdn.datatables.net/1.10.4/js/jquery.dataTables.min.js"></script>
		<script type="text/javascript" language="javascript" src="//cdn.datatables.net/plug-ins/9dcbecd42ad/integration/bootstrap/3/dataTables.bootstrap.js"></script>

	
	
	<style type="text/css" class="init">

	</style>
		
	
	<script type="text/javascript" language="javascript" >


$(document).ready(function() {
    $('#expensive').dataTable( {
        
        "ordering": false,
        "info":     false
        
    } );
    $('#blacklist').dataTable( {
        
        "ordering": false,
        "info":     false
        
    } );
    $('#minmax').dataTable( {
        
        "ordering": false,
        "info":     false
        
    } );
    
    $('#notorderedbydate').dataTable( {
        
        "ordering": false,
        "info":     false
        
    } );
} );

	</script>

  </head>
  <body>
    <nav class="navbar navbar-default" role="navigation">
  <div class="container-fluid">
    <!-- Brand and toggle get grouped for better mobile display -->
    <div class="navbar-header">
      <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1">
        <span class="sr-only">Toggle navigation</span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
      </button>
        <a class="navbar-brand" href="#"><img width="100" src="https://www.freshdirect.com/media/images/navigation/global_nav/fd_logo_on.gif"></img></a>
    </div>

    <!-- Collect the nav links, forms, and other content for toggling -->
    <div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
      <ul class="nav navbar-nav">
          <li><h4>Welcome to FreshDirect!</h4></li>	
      </ul>
      <ul class="nav navbar-nav navbar-right">
          <li><div class="navbar-form">
              <button type="button" class="btn btn-default">My Cart&nbsp;<span class="badge">4</span></button>
              </div></li>
          <li class="dropdown">
              <a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-expanded="false">${sessionScope.userName}<span class="caret"></span></a>
              <ul class="dropdown-menu" role="menu">
                <li><a href="${pageContext.request.contextPath}/user/myaccount">My Account</a></li>
                <li><a href="${pageContext.request.contextPath}/user/myorders">My orders</a></li>
                <li><a href="">FAQ</a></li>
                <li class="divider"></li>
                <li><form action="${pageContext.request.contextPath}/userout" method="post">
    <input type="submit" value="Logout" />
</form></li>
      </ul>
    </div><!-- /.navbar-collapse -->
  </div><!-- /.container-fluid -->
</nav>

<div class="container">
<div class="row">
        <nav class="navbar navbar-default" role="navigation" style="background-color: #728d4b; border-radius: 10px;">
  <div class="container-fluid" style="background-color: #728d4b; border-radius: 10px; ">
    <!-- Brand and toggle get grouped for better mobile display -->
    <div class="navbar-header">
      <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#bs-example-navbar-collapse-2">
        <span class="sr-only">Toggle navigation</span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
      </button>
    </div>
    <!-- Collect the nav links, forms, and other content for toggling -->
    <div class="collapse navbar-collapse" style="background-color: #728d4b" id="bs-example-navbar-collapse-2">
      <ul class="nav navbar-nav" style="background-color: #728d4b">
        <li style="background-color: #728d4b"><a style="background-color: #728d4b; color:white; font-size: 18px; font-weight: bold; font-family: 'Oswald', sans-serif;" href="${baseUrl}/products/fruits">FRUITS</a></li>
        <li><a style="color:white; font-size: 18px; font-weight: bold; font-family: 'Oswald', sans-serif; " href="#">VEGETABLES</a></li>
        <li><a style="color:white; font-size: 18px; font-weight: bold; font-family: 'Oswald', sans-serif;" href="#">SEAFOOD</a></li>
        <li><a style="color:white; font-size: 18px; font-weight: bold; font-family: 'Oswald', sans-serif;" href="#">MEAT</a></li>
        <li><a style="color:white; font-size: 18px; font-weight: bold; font-family: 'Oswald', sans-serif;" href="#">DAIRY</a></li>
        <li><a style="color:white; font-size: 18px; font-weight: bold; font-family: 'Oswald', sans-serif;" href="#">BAKERY & PASTRY</a></li>
        <li><a style="color:white; font-size: 18px; font-weight: bold; font-family: 'Oswald', sans-serif;" href="#">CEREAL & PALSES</a></li>
        <li><a style="color:white; font-size: 18px; font-weight: bold; font-family: 'Oswald', sans-serif;" href="#">FROZEN</a></li>
        <li><a style="color:white; font-size: 18px; font-weight: bold; font-family: 'Oswald', sans-serif;" href="#">CANNED FOOD</a></li>
        <li><a style="color:white; font-size: 18px; font-weight: bold; font-family: 'Oswald', sans-serif;" href="#">FLOWERS</a></li>
        <li><a style="color:white; font-size: 18px; font-weight: bold; font-family: 'Oswald', sans-serif;" href="#">PET</a></li>

      </ul>
    </div><!-- /.navbar-collapse -->
  </div><!-- /.container-fluid -->
</nav>
  
        
<div class="panel-group" id="accordion" role="tablist" aria-multiselectable="true">
<sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost:3306/freshDirect"
     user="username"  password="username"/>

<sql:query dataSource="${snapshot}" var="result1">
call faq1();
</sql:query>
<sql:query dataSource="${snapshot}" var="result3">
call faq3();
</sql:query>
<sql:query dataSource="${snapshot}" var="result4">
call faq4();
</sql:query>
<sql:query dataSource="${snapshot}" var="result5">
call faq5();
</sql:query>    
 
  <div class="panel panel-default">
    <div class="panel-heading" role="tab" id="headingOne">
      <h4 class="panel-title">
        <a data-toggle="collapse" data-parent="#accordion" href="#collapseOne" aria-expanded="false" aria-controls="collapseOne">
          Most Expensive Product for each Group
        </a>
      </h4>
    </div>
    <div id="collapseOne" class="panel-collapse collapse in" role="tabpanel" aria-labelledby="headingOne">
      <div class="panel-body">
        <div class="row-fluid" style="text-align: center;"><h2><div style="font-family: 'Oswald', sans-serif; font-weight: bold; color:#FFAE19;">Most Expensive Product for each Group</div></h2><br></div>
                    <div class="row-fluid">  
        <table id="expensive" class="table table-striped table-bordered" cellspacing="0" width="100%">
				<thead>
					<tr>
						<th>Product Group</th>
                                                <th></th>
						<th>Product Name</th>
						<th>Price</th>
						
					</tr>
				</thead>


				<tbody>
<c:forEach var="row" items="${result4.rows}">
<tr>
<td><c:out value="${row.group_name}"/></td>
<td><c:out value="${row.prod_name}"/></td>
<td><c:out value="${row.list_price}"/></td>
<td><img src="${row.Image}" style="height:50px"></td>
</tr>
</c:forEach>
				</tbody>
			</table>  
          
                    </div>
      </div>
    </div>
 </div>
  <div class="panel panel-default">
    <div class="panel-heading" role="tab" id="headingTwo">
      <h4 class="panel-title">
        <a class="collapsed" data-toggle="collapse" data-parent="#accordion" href="#collapseTwo" aria-expanded="false" aria-controls="collapseTwo">
          Products that have never been order
        </a>
      </h4>
    </div>
    <div id="collapseTwo" class="panel-collapse collapse" role="tabpanel" aria-labelledby="headingTwo">
      <div class="panel-body">
<div class="row-fluid" style="text-align: center;"><h2><div style="font-family: 'Oswald', sans-serif; font-weight: bold; color:#FFAE19;">Products that have never been order</div></h2><br></div>
                    <div class="row-fluid">  
        <table id="blacklist" class="table table-striped table-bordered" cellspacing="0" width="100%">
				<thead>
					<tr>
						<th>Name</th>
                                                <th></th>
						<th>Group</th>
						<th>Price</th>
						
					</tr>
				</thead>


				<tbody>
                              <c:forEach var="row" items="${result5.rows}">
<tr>
<td><c:out value="${row.prod_name}"/></td>
<td><img src="${row.Image}" style="height:50px"></td>
<td><c:out value="${row.group_name}"/></td>
<td><c:out value="${row.list_price}"/></td>
</tr>
</c:forEach>
			
				</tbody>
			</table>  
          
                    </div>
      </div>
    </div>
  </div>
  <div class="panel panel-default">
    <div class="panel-heading" role="tab" id="headingThree">
      <h4 class="panel-title">
        <a class="collapsed" data-toggle="collapse" data-parent="#accordion" href="#collapseThree" aria-expanded="false" aria-controls="collapseThree">
         Best Seller in terms of units Sold
        </a>
      </h4>
    </div>
    <div id="collapseThree" class="panel-collapse collapse" role="tabpanel" aria-labelledby="headingThree">
      <div class="panel-body">
          <h3><c:forEach var="row" items="${result3.rows}">
                 <table>
<tr>
<td><c:out value="${row.prod_name}"/></td>
</tr>
</c:forEach>
</table></h3>
      </div>
    </div>
  </div>   
    
 <div class="panel panel-default">
    <div class="panel-heading" role="tab" id="headingFour">
      <h4 class="panel-title">
        <a class="collapsed" data-toggle="collapse" data-parent="#accordion" href="#collapseFour" aria-expanded="false" aria-controls="collapseFour">
         For each product, the lowest and highest ever order sum
        </a>
      </h4>
    </div>
    <div id="collapseFour" class="panel-collapse collapse" role="tabpanel" aria-labelledby="headingFour">
      <div class="panel-body">
<div class="row-fluid" style="text-align: center;"><h2><div style="font-family: 'Oswald', sans-serif; font-weight: bold; color:#FFAE19;">Min & Max order of all Products Sold</div></h2><br></div>
                    <div class="row-fluid">  
        <table id="minmax" class="table table-striped table-bordered" cellspacing="0" width="100%">
				<thead>
					<tr>
					
						<th>Name</th>
                                                <th></th>
                                                <th>Price</th>
						<th>Min Order</th>
                                                <th>Max Order</th>
					</tr>
				</thead>
				<tbody>
                              <c:forEach var="row" items="${result1.rows}">
<tr>

<td><c:out value="${row.prod_name}"/></td>
<td><img src="${row.image}" style="height:50px"></td>
<td><c:out value="${row.list_price}"/></td>
<td><c:out value="${row.MIN}"/></td>
<td><c:out value="${row.MAX}"/></td>
</tr>
</c:forEach>
			
				</tbody>
			</table>  
                    </div>
      </div>
      </div>
    </div>
  
  <div class="panel panel-default">
    <div class="panel-heading" role="tab" id="headingFive">
      <h4 class="panel-title">
        <a class="collapsed" data-toggle="collapse" data-parent="#accordion" href="#collapseFive" aria-expanded="false" aria-controls="collapseFive">
         Products that were not ordered in several date
        </a>
      </h4>
    </div>
    <div id="collapseFive" class="panel-collapse collapse" role="tabpanel" aria-labelledby="headingFive">
      <div class="panel-body">
<div class="row-fluid" style="text-align: center;"><h2><div style="font-family: 'Oswald', sans-serif; font-weight: bold; color:#FFAE19;">Products that were not ordered in several date</div></h2><br></div>
<h4>Select  Month & Year</h4>
<form:form action="faq" commandName="view"  class="navbar-form ">
                            <div class="form-group">
                              <label for="sel1">Month</label>
                               <form:select class="form-control"  path="m">
                                  <form:option value="01">01</form:option>
                                  <form:option value="02">02</form:option>
                                  <form:option value="03">03</form:option>
                                  <form:option value="04">04</form:option>
                                  <form:option value="05">05</form:option>
                                  <form:option value="06">06</form:option>
                                  <form:option value="07">07</form:option>
                                  <form:option value="08">08</form:option>
                                  <form:option value="09">09</form:option>
                                  <form:option value="10">10</form:option>
                                  <form:option value="11">11</form:option>
                                  <form:option value="12">12</form:option>
                              </form:select>
                              <label for="sel1">Year</label>
                                <form:select class="form-control"  path="y">
                                  <form:option value="2000">2000</form:option>
                                  <form:option value="2001">2001</form:option>
                                  <form:option value="2002">2002</form:option>
                                  <form:option value="2003">2002</form:option>
                                  <form:option value="2004">2004</form:option>
                                  <form:option value="2005">2005</form:option>
                                  <form:option value="2006">2006</form:option>
                                  <form:option value="2007">2007</form:option>
                                  <form:option value="2008">2008</form:option>
                                  <form:option value="2009">2009</form:option>
                                  <form:option value="2010">2010</form:option>
                                  <form:option value="2011">2011</form:option>
                                  <form:option value="2012">2012</form:option>
                                  <form:option value="2013">2013</form:option>
                                  <form:option value="2014">2014</form:option>
                              </form:select>
                            </div>   
    <button type="submit" class="btn btn-default">View</button>
     </form:form>
<br/><br/>
<c:if test="${m != null}">
<sql:query dataSource="${snapshot}" var="result6">
call faq6(${m},${y});
</sql:query>   
</c:if>
<div class="row-fluid">  
        <table id="notorderedbydate" class="table table-striped table-bordered" cellspacing="0" width="100%">
				<thead>
					<tr>
						<th>Name</th>
						
					</tr>
				</thead>

				<tfoot>
					<tr>
						<th>Name</th>
						
					</tr>
				</tfoot>

				<tbody>
				<c:forEach var="row" items="${result6.rows}">
<tr>
<td><c:out value="${row.prod_name}"/></td>
<td><img src="${row.Image}" style="height:50px"></td>
<td><c:out value="${row.group_name}"/></td>
<td><c:out value="${row.list_price}"/></td>
</tr>
</c:forEach>	
			
				</tbody>
			</table>  
          
                    </div>
      </div>
      </div>
    </div>  
 
    <div class="panel panel-default">
    <div class="panel-heading" role="tab" id="headingSix">
      <h4 class="panel-title">
        <a class="collapsed" data-toggle="collapse" data-parent="#accordion" href="#collapseSix" aria-expanded="false" aria-controls="collapseSix">
         On which days of several month & year the total sum of the value of all orders
taken was larger than 10,000 Euros

        </a>
      </h4>
    </div>
    <div id="collapseSix" class="panel-collapse collapse" role="tabpanel" aria-labelledby="headingSix">
      <div class="panel-body">
<div class="row-fluid" style="text-align: center;"><h2><div style="font-family: 'Oswald', sans-serif; font-weight: bold; color:#FFAE19;">Days on which , orders
taken was larger than 10,000 Euros</div></h2><br></div>
<h4>Select  Month & Year</h4>
 <form:form action="faq" commandName="view"  class="navbar-form ">
                            <div class="form-group">
                              <label for="sel1">Month</label>
                              <form:select class="form-control"  path="m">
                                  <form:option value="01">01</form:option>
                                  <form:option value="02">02</form:option>
                                  <form:option value="03">03</form:option>
                                  <form:option value="04">04</form:option>
                                  <form:option value="05">05</form:option>
                                  <form:option value="06">06</form:option>
                                  <form:option value="07">07</form:option>
                                  <form:option value="08">08</form:option>
                                  <form:option value="09">09</form:option>
                                  <form:option value="10">10</form:option>
                                  <form:option value="11">11</form:option>
                                  <form:option value="12">12</form:option>
                              </form:select>
                              <label for="sel1">Year</label>
                                <form:select class="form-control"  path="y">
                                  <form:option value="2000">2000</form:option>
                                  <form:option value="2001">2001</form:option>
                                  <form:option value="2002">2002</form:option>
                                  <form:option value="2003">2002</form:option>
                                  <form:option value="2004">2004</form:option>
                                  <form:option value="2005">2005</form:option>
                                  <form:option value="2006">2006</form:option>
                                  <form:option value="2007">2007</form:option>
                                  <form:option value="2008">2008</form:option>
                                  <form:option value="2009">2009</form:option>
                                  <form:option value="2010">2010</form:option>
                                  <form:option value="2011">2011</form:option>
                                  <form:option value="2012">2012</form:option>
                                  <form:option value="2013">2013</form:option>
                                  <form:option value="2014">2014</form:option>
                              </form:select>
                            </div>   
    <button type="submit" class="btn btn-default">View</button>
      </form:form>
<c:if test="${m !=null}">
<sql:query dataSource="${snapshot}" var="result6">
call faq2(${y},${m});
</sql:query>      
</c:if>
<div class="row-fluid">  
        <table id="notorderedbydate" class="table table-striped table-bordered" cellspacing="0" width="100%">
				<thead>
					<tr>
						<th>Days</th>
						<th>Total Cost</th>
						
					</tr>
				</thead>

				<tfoot>
					<tr>
						<th>Days</th>
						<th>Total Cost</th>
						
					</tr>
				</tfoot>


        <tbody>
  <c:forEach var="row" items="${result2.rows}">
<tr>

<td><c:out value="${row.Days}"/></td>
<td><c:out value="${row.A}"/></td>
</tr>
</c:forEach>         
       </tbody>
   
				
			
			</table>  


</div>
   
                    </div>
      </div>
      </div>
    </div>  
     
  </div>

      

      
    
        
        

        
        
        

        
        
        
        
    
  </body>
</html>